!/bin/bash

set -euo pipefail

# ------------------------------
# CONSTANTS AND DEFAULTS
# ------------------------------
HIST_PREFIX="dba_archive_"          # Historical table prefix
MYSQLCLIENT="/usr/local/mysql/bin/mysql"    # MySQL client path (adjust if needed)
LOGFILE="adj_matial.log"
TIMESTAMP=$(date +%Y%m%d) # High precision for concurrency safety
BEFORETIMESTAMP=$(date -d "-1 day" "+%Y%m%d")
DAYS_TO_KEEP=3                  # Keep histories for last N days
DB_NAME='report'
ACTIVE_TABLE='out_not_into'
password='abC123_sdfjl12('
user='root'
host='localhost'

WEBHOOK_URL="https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=c7ef1621-3914-4be7-ac5b-d3f70938f240"
function send_alert(){
  MESSAGE=${1:-"物料库表删除失败"}
  PAYLOAD=$(cat <<EOF
  {
     "msgtype": "text",
     "text": {
       "content": "$MESSAGE",
       "mentioned_list": ["@all"]
    }
  }
EOF
)

# 发送请求
response=$(curl -s -X POST \
  -H "Content-Type: application/json" \
  -d "$PAYLOAD" \
  $WEBHOOK_URL)

# 检查执行结果
if [[ $? -eq 0 ]]; then
  echo "消息发送成功: $response" >>$LOGFILE
else
  echo "消息发送失败">>$LOGFILE
fi
}
# ------------------------------
# PREPARE ENVIRONMENT
# ------------------------------
touch "$LOGFILE"

echo "=====[ $(date) ] Starting table rotation and cleanup =====" | tee -a "$LOGFILE"
echo "Parameters:" | tee -a "$LOGFILE"
echo "  DB: $DB_NAME" | tee -a "$LOGFILE"
echo "  Active Table: $ACTIVE_TABLE" | tee -a "$LOGFILE"
echo "  Historical Prefix: $HIST_PREFIX" | tee -a "$LOGFILE"
echo "  Retention Policy: Keep last ${DAYS_TO_KEEP} days of histories" | tee -a "$LOGFILE"

# ------------------------------
# STAGE 1: ROTATE ACTIVE TABLE TO HISTORICAL
# ------------------------------
NEW_TABLENAME="${HIST_PREFIX}${ACTIVE_TABLE}_${TIMESTAMP}"
BEFORE_TABLENAME="${HIST_PREFIX}${ACTIVE_TABLE}_${BEFORETIMESTAMP}"

echo "&#128161; Attempting to rename table..." | tee -a "$LOGFILE"

QUERY_CHECK="SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = '$NEW_TABLENAME';"
CNT=$($MYSQLCLIENT -h$host -u$user -p$password -Nse "$QUERY_CHECK")
if [ $CNT -eq  0 ]; then
  QUERY="START TRANSACTION;
      RENAME TABLE \`$DB_NAME\`.\`$ACTIVE_TABLE\` TO \`$DB_NAME\`.\`$NEW_TABLENAME\`;
      create table \`$DB_NAME\`.\`$ACTIVE_TABLE\` like  \`$DB_NAME\`.\`$NEW_TABLENAME\`;
      COMMIT;"
  $MYSQLCLIENT -h$host -u$user -p"$password" --skip-column-names -e "$QUERY" >/dev/null 2>&1
  if [ $? -eq 0 ]; then
    echo "&#9989; 成功创建历史表: $DB_NAME.$NEW_TABLENAME" | tee -a "$LOGFILE"
  else
    QUERY_EXIST=" SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = '$ACTIVE_TABLE'; "
    EXISTCNT=$($MYSQLCLIENT -h$host -u$user -p$password --skip-column-names -e "$QUERY_EXIST" >/dev/null 2>&1)
    if [ $EXISTCNT -eq 0 ]; then
      CREATEQUERY="create table \`$DB_NAME\`.\`$ACTIVE_TABLE\` like  \`$DB_NAME\`.\`$BEFORE_TABLENAME\`;"
      $MYSQLCLIENT -h$host -u$user -p"$password" --skip-column-names -e "$CREATEQUERY" >/dev/null 2>&1
      if [ $? -eq 0 ]; then
         echo "&#9989; 成功创建原始表: $DB_NAME.$ACTIVE_TABLE" | tee -a "$LOGFILE"
      else
         echo "删除表失败错误信息:" | tee -a "$LOGFILE"
         send_alert "创建原始表失败${ACTIVE_TALBE},需要DBA介入处理"
      fi
    fi
  fi
else
  echo "&#9989; 创建历史表已经存在: $DB_NAME.$NEW_TABLENAME" | tee -a "$LOGFILE"
fi


# ------------------------------
# ------------------------------
# STAGE 2: CLEANUP HISTORICAL TABLES OLDER THAN ${DAYS_TO_KEEP} DAYS
# ------------------------------
echo "&#128269; 正在查找待删除的历史表..." | tee -a "$LOGFILE"

# 计算截止时间（3天前的相同时间）
CUTOFF_TIMESTAMP=$(date -d "${DAYS_TO_KEEP} days ago" +%Y%m%d)

# 查找所有匹配前缀且时间戳 <= CUTOFF_TIMESTAMP 的表
OLD_TABLES=$($MYSQLCLIENT -h$host -u$user -p$password -Nse "
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = '$DB_NAME'
      AND TABLE_NAME LIKE '${HIST_PREFIX}${ACTIVE_TABLE}\_%'
      AND TABLE_NAME <= '${HIST_PREFIX}${ACTIVE_TABLE}_${CUTOFF_TIMESTAMP}'
    ORDER BY TABLE_NAME DESC;
")

# 过滤空结果并转换为数组
for table in $OLD_TABLES; do
  echo "&#128293; 正在删除表: $DB_NAME.$table" | tee -a "$LOGFILE"
  $MYSQLCLIENT -h$host -u$user -p"$password" -e "DROP TABLE \`$DB_NAME\`.\`$table\`;" >> "$LOGFILE" 2>&1
  if [ $? -ne 0 ]; then
    echo "&#10060; 删除失败: $table" | tee -a "$LOGFILE"
  else
    echo "&#9989; 成功删除: $table" | tee -a "$LOGFILE"
  fi
done

echo "=====[ $(date) ] Operation completed =====" | tee -a "$LOGFILE"